﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Yipusoft.Account.Model;
using Yipusoft.Common.BLL;
using Yipusoft.Task.BLL;
using System.Text;
using System.Data;
using System.IO;
public partial class Evaluate_TaskReportAll : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindYearList();
            BindMonthList();
            BindDepartment();
            init();
            Chart1.Series[0].Type = Dundas.Charting.WebControl.SeriesChartType.Bar;

        }
    }
    void BindDepartment()
    {
        string departmentId = Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).DepartmentID;
        List<DepartmentInfo> list = Yipusoft.Account.BLL.Department.GetList(" departmentId like '%0" + departmentId.Substring(5, 1) + "0' and departmentId<>'13000" + departmentId.Substring(5, 1) + "0'");
        ddlDepartment.DataValueField = "DepartmentId";
        ddlDepartment.DataTextField = "ShortName";
        ddlDepartment.DataSource = list;
        ddlDepartment.DataBind();
        ddlDepartment.SelectedIndex = 0;
    }
    void init()
    {
        CreateTable();
    }

    private void BindYearList()
    {
        for (int i = 2008; i <= 2050; i++)
        {
            ListItem li = new ListItem();
            li.Text = i.ToString() + "年";
            li.Value = i.ToString();
            YearList.Items.Add(li);
        }
        YearList.SelectedValue = DateTime.Now.Year.ToString();
    }
    private void BindMonthList()
    {
        for (int i = 1; i <= 12; i++)
        {
            ListItem li = new ListItem();
            li.Text = i.ToString() + "月";
            li.Value = i.ToString();
            MonthList.Items.Add(li);
        }
        MonthList.SelectedIndex = MonthList.Items.IndexOf(MonthList.Items.FindByValue(DateTime.Now.Month.ToString()));
    }
    private void CreateTable()
    {
        PlaceHolder.Controls.Clear();
        string departmentid = ddlDepartment.SelectedValue;

        string DepartmentWhere = "departmentid like '" + departmentid.Substring(0, 4) + RadioButtonList1.SelectedValue + "%'";


        string UseridWhere = "userid like ''" + departmentid.Substring(0, 4) + RadioButtonList1.SelectedValue + "%''";


        DataView pdv = SplitPage.ExecuteSQL("Select departmentid,ShortName From Account_Departments where " + DepartmentWhere + "order by departmentid").Tables[0].DefaultView;

        string departmentids = "";
        for (int i = 0; i < pdv.Count; i++)
        {
            departmentids += ", SUM(CASE WHEN UserId = ''" + pdv[i][0] + "'' THEN Score END) AS D" + pdv[i][0] + "";
        }
        StringBuilder sql = new StringBuilder();


        sql.AppendLine("DECLARE @SQL varchar(8000)");
        sql.AppendLine("SET @SQL = ''");
        sql.AppendLine("SET @SQL = 'SELECT TaskId " + departmentids + " FROM TaskUsers WHERE (" + UseridWhere + ") and  TaskId IN (SELECT TaskId FROM Tasks WHERE YEAR(EndDate) =" + YearList.SelectedValue + " AND MONTH(EndDate)=" + MonthList.SelectedValue + " AND CreateDepartment like ''" + departmentid.Substring(0, 6) + "%'') GROUP BY TaskId'");
        sql.AppendLine("EXEC(@SQL)");


        DataView dv = SplitPage.ExecuteSQL(sql.ToString()).Tables[0].DefaultView;

        Table tb = new Table();
        tb.Caption = "<span>" + YearList.SelectedItem.Text + MonthList.SelectedItem.Text + "工作任务得分情况统计表</span>";
        tb.CellPadding = 4;
        tb.CellSpacing = 0;
        TableRow tr = new TableRow();
        TableCell tc = new TableCell();
        tr.CssClass = "grid_header";
        tc = new TableCell();
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Text = "任务名称";
        tc.Attributes.Add("style", "border-color:#CEE7F7;background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

        tr.Cells.Add(tc);

        int col = pdv.Count;

        for (int i = 0; i < col; i++)
        {
            tc = new TableCell();
            tc.HorizontalAlign = HorizontalAlign.Right;
            tc.Text = pdv[i][1].ToString();
            tc.Attributes.Add("style", "border-color:#CEE7F7;background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

            tr.Cells.Add(tc);
            tb.Rows.Add(tr);
        }
        int row = 1;
        foreach (DataRowView drv in dv)
        {
            tr = new TableRow();
            tr.CssClass = "grid_item";
            tc = new TableCell();
            tc.BackColor = System.Drawing.Color.White;
            tc.Attributes.Add("style", "border-color:#CEE7F7;background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

            string title = Task.GetTask((int)drv[0]).Title.Length <= 16 ? Task.GetTask((int)drv[0]).Title : Task.GetTask((int)drv[0]).Title.Substring(0, 16);

            tc.Text = title;
            tr.Cells.Add(tc);
            for (int i = 1; i <= col; i++)
            {
                tc = new TableCell();
                tc.BackColor = System.Drawing.Color.White;
                tc.HorizontalAlign = HorizontalAlign.Right;
                string score = "";
                if (drv[i] == DBNull.Value)
                {
                    tc.Text = "未下达";
                }
                else
                {
                    if ((decimal)drv[i] > 0)
                        score = ((decimal)drv[i]).ToString("0");
                    else
                        score = "0";
                    tc.Text = "<a href=TaskView.aspx?RootId=" + drv[0].ToString() + "&UserId=" + dv.Table.Columns[i].ColumnName.Replace("D", "") + ">" + score + "</a>";

                }
                tc.Attributes.Add("style", "border-color:#CEE7F7;background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");
                tc.HorizontalAlign = HorizontalAlign.Center;
                tr.Cells.Add(tc);
            }
            tb.Rows.Add(tr);
            row++;
        }



        tr = new TableRow();
        tr.CssClass = "grid_item";
        tc = new TableCell();
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Text = "合计";
        tc.Attributes.Add("style", "border-color:#CEE7F7;background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

        sql = new StringBuilder("Select departmentid, a.ShortName,(Select Sum(Score) From TaskUsers left join Tasks on taskUsers.taskid=tasks.taskid Where UserID=a.DepartmentID And taskusers.TaskID in(Select TaskID From Tasks Where CreateDepartment like '" + departmentid.Substring(0, 6) + "%' and YEAR(EndDate) ='" + YearList.SelectedValue + "' AND MONTH(EndDate)='" + MonthList.SelectedValue + "' )) as Score  From Account_Departments as  a where " + DepartmentWhere + " Group by a.DepartmentID,a.ShortName order by DepartmentId");
        pdv = SplitPage.ExecuteSQL(sql.ToString()).Tables[0].DefaultView;

        tr.Cells.Add(tc);
        for (int i = 0; i < col; i++)
        {
            tc = new TableCell();
            tc.HorizontalAlign = HorizontalAlign.Right;
            if (pdv[i][2] != DBNull.Value)
            {
                if (pdv.Count > 0)
                {
                    if (decimal.Parse(pdv[i][2].ToString()) > 0)
                    {
                        tc.Text = ((decimal)pdv[i][2]).ToString("0");
                    }
                    else
                    {
                        tc.Text = "0";
                    }
                }
                else
                {
                    tc.Text = "0";
                }
            }
            else
            {
                tc.Text = "没有任务";
            }
            tc.Attributes.Add("style", "border-color:#CEE7F7;background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

            tc.HorizontalAlign = HorizontalAlign.Center;
            tr.Cells.Add(tc);
            tb.Rows.Add(tr);
        }
        PlaceHolder.Controls.Add(tb);
        BindChart();
    }


    private void BindChart()
    {
        string departmentid = ddlDepartment.SelectedValue;

        string DepartmentWhere;
        string UseridWhere = "";

        DepartmentWhere = "departmentid like '" + departmentid.Substring(0, 4) + RadioButtonList1.SelectedValue + "%'";
        UseridWhere = "userid like ''" + departmentid.Substring(0, 4) + RadioButtonList1.SelectedValue + "%''";
        DataView pdv = null;

        string sql = "Select departmentid, a.ShortName,(Select Sum(Score) From TaskUsers left join Tasks on taskUsers.taskid=tasks.taskid Where UserID=a.DepartmentID And taskusers.TaskID in(Select TaskID From Tasks Where CreateDepartment like '" + departmentid.Substring(0, 6) + "%' and YEAR(EndDate) ='" + YearList.SelectedValue + "' AND MONTH(EndDate)='" + MonthList.SelectedValue + "' )) as Score  From Account_Departments as  a where " + DepartmentWhere + " Group by a.DepartmentID,a.ShortName order by Score desc";
        pdv = SplitPage.ExecuteSQL(sql).Tables[0].DefaultView;
        Chart1.Series[0].Type = Dundas.Charting.WebControl.SeriesChartType.Bar;
        Chart1.Titles["Title"].Text = YearList.SelectedItem.Text + MonthList.SelectedItem.Text + "各单位工作任务得分情况统计表";
        Chart1.ChartAreas["Default"].AxisX.LabelsAutoFit = false;
        Chart1.Series["得分"].Points.DataBindXY(pdv, "ShortName", pdv, "Score");

    }
    protected void ButtonEvaluate_Click(object sender, EventArgs e)
    {
        init();
    }
    protected void lkbExport_Click(object sender, EventArgs e)
    {
        ExportData("application/ms-excel", DateTime.Now.ToString("yyMdHm") + ".xls", false);
    }

    private void ExportData(string FileType, string FileName, bool isMid)
    {
        CreateTable1(isMid);
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = FileType;
        this.EnableViewState = false;
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        Response.Write(tw.ToString());
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //base.VerifyRenderingInServerForm(control);
    }
    private void CreateTable1(bool isMid)
    {
        string departmentid = ddlDepartment.SelectedValue;

        string DepartmentWhere = "";
        string UseridWhere = "";
        if (isMid)
        {
            DepartmentWhere = "departmentid like '" + departmentid.Substring(0, 4) + "3%'";
            UseridWhere = "userid like ''" + departmentid.Substring(0, 4) + "3%''";
        }
        else
        {
            DepartmentWhere = "departmentid like '" + departmentid.Substring(0, 4) + "2%'";
            UseridWhere = "userid like ''" + departmentid.Substring(0, 4) + "2%''";
        }


        DataView pdv = null;
        if (departmentid.Substring(6, 1) != "0")
        {
            pdv = SplitPage.ExecuteSQL("Select departmentid, a.ShortName,(Select Sum(Score)/sum(initScore) From TaskUsers left join Tasks on taskUsers.taskid=tasks.taskid Where UserID=a.DepartmentID And taskusers.TaskID in(Select TaskID From Tasks Where CreateDepartment='" + departmentid + "' and YEAR(EndDate) ='" + YearList.SelectedValue + "' AND MONTH(EndDate)='" + MonthList.SelectedValue + "' )) as Score  From Account_Departments as  a where " + DepartmentWhere + " Group by a.DepartmentID,a.ShortName order by Score desc").Tables[0].DefaultView;

        }
        else
        {
            pdv = SplitPage.ExecuteSQL("Select departmentid, a.ShortName,(Select Sum(Score)/sum(initScore) From TaskUsers left join Tasks on taskUsers.taskid=tasks.taskid Where UserID=a.DepartmentID And taskusers.TaskID in(Select TaskID From Tasks Where CreateDepartment like '" + departmentid.Substring(0, 5) + "%' and YEAR(EndDate) ='" + YearList.SelectedValue + "' AND MONTH(EndDate)='" + MonthList.SelectedValue + "' )) as Score  From Account_Departments as  a where " + DepartmentWhere + " Group by a.DepartmentID,a.ShortName order by Score desc").Tables[0].DefaultView;

        }

        string departmentids = "";
        for (int i = 0; i < pdv.Count; i++)
        {
            departmentids += ", SUM(CASE WHEN UserId = ''" + pdv[i][0] + "'' THEN Score END) AS D" + pdv[i][0] + "";
        }

        // departmentids = departmentids.Substring(0, departmentids.Length - 1);
        //departmentids = " departmentid in (" + departmentids + ")";

        StringBuilder sql = new StringBuilder();
        if (departmentid.Substring(6, 1) == "0")
        {

            sql.AppendLine("DECLARE @SQL varchar(8000)");
            sql.AppendLine("SET @SQL = ''");
            //sql.AppendLine("SELECT @SQL = @SQL + ', SUM(CASE WHEN UserId = ''' + DepartmentId + ''' THEN Score END) AS D' + DepartmentId FROM Account_Departments WHERE " + departmentids + "");
            sql.AppendLine("SET @SQL = 'SELECT TaskId " + departmentids + " FROM TaskUsers WHERE (" + UseridWhere + ") and  TaskId IN (SELECT TaskId FROM Tasks WHERE YEAR(EndDate) =" + YearList.SelectedValue + " AND MONTH(EndDate)=" + MonthList.SelectedValue + " AND CreateDepartment like ''" + Session["DepartmentID"].ToString().Substring(0, 5) + "%'') GROUP BY TaskId'");
            sql.AppendLine("EXEC(@SQL)");

        }
        else
        {
            sql.AppendLine("DECLARE @SQL varchar(8000)");
            sql.AppendLine("SET @SQL = ''");
            //sql.AppendLine("SELECT @SQL = @SQL + ', SUM(CASE WHEN UserId = ''' + DepartmentId + ''' THEN Score END) AS D' + DepartmentId FROM Account_Departments WHERE " + departmentids + "");
            sql.AppendLine("SET @SQL = 'SELECT TaskId " + departmentids + " FROM TaskUsers WHERE (" + UseridWhere + ") and  TaskId IN (SELECT TaskId FROM Tasks WHERE YEAR(EndDate) =" + YearList.SelectedValue + " AND MONTH(EndDate)=" + MonthList.SelectedValue + " AND CreateDepartment=''" + Session["DepartmentID"].ToString() + "'') GROUP BY TaskId'");
            sql.AppendLine("EXEC(@SQL)");
        }
        DataView dv = SplitPage.ExecuteSQL(sql.ToString()).Tables[0].DefaultView;

        Table tb = new Table();
        if (isMid)
        {
            tb.Caption = "<span>" + YearList.SelectedItem.Text + MonthList.SelectedItem.Text + "中队工作任务得分情况统计表</span>";
        }
        else
        {
            tb.Caption = "<span>" + YearList.SelectedItem.Text + MonthList.SelectedItem.Text + "大队工作任务得分情况统计表</span>";
        }

        tb.Width = Unit.Percentage(100);
        tb.CellPadding = 4;
        tb.CellSpacing = 0;
        TableRow tr = new TableRow();
        TableCell tc = new TableCell();
        tr.CssClass = "grid_header";
        //tc.Text = "";
        //tc.Width = Unit.Pixel(30);
        //tr.Cells.Add(tc);
        tc = new TableCell();
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Text = "任务名称";
        tc.Attributes.Add("style", "background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

        tr.Cells.Add(tc);

        int col = pdv.Count;

        for (int i = 0; i < col; i++)
        {
            tc = new TableCell();
            tc.HorizontalAlign = HorizontalAlign.Right;
            tc.Text = pdv[i][1].ToString();
            tc.Attributes.Add("style", "background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

            tr.Cells.Add(tc);
            tb.Rows.Add(tr);
        }
        int row = 1;
        foreach (DataRowView drv in dv)
        {
            tr = new TableRow();
            tr.CssClass = "grid_item";
            tc = new TableCell();
            tc.BackColor = System.Drawing.Color.White;
            tc.Attributes.Add("style", "background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

            string title = Task.GetTask((int)drv[0]).Title.Length <= 16 ? Task.GetTask((int)drv[0]).Title : Task.GetTask((int)drv[0]).Title.Substring(0, 16);

            tc.Text = title;
            tr.Cells.Add(tc);
            for (int i = 1; i <= col; i++)
            {
                tc = new TableCell();
                tc.BackColor = System.Drawing.Color.White;
                tc.HorizontalAlign = HorizontalAlign.Right;
                string score = "";
                if (drv[i] == DBNull.Value)
                {
                    tc.Text = "未下达";
                }
                else
                {
                    if ((decimal)drv[i] > 0)
                        score = ((decimal)drv[i]).ToString("0");
                    else
                        score = "0";
                    tc.Text = score;

                }
                tc.Attributes.Add("style", "background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");
                tc.HorizontalAlign = HorizontalAlign.Center;
                tr.Cells.Add(tc);
            }
            tb.Rows.Add(tr);
            row++;
        }



        tr = new TableRow();
        tr.CssClass = "grid_item";
        tc = new TableCell();
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Text = "合计";
        tc.Attributes.Add("style", "background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

        tr.Cells.Add(tc);
        for (int i = 0; i < col; i++)
        {
            tc = new TableCell();
            tc.HorizontalAlign = HorizontalAlign.Right;
            if (pdv[i][2] != DBNull.Value)
            {
                if (pdv.Count > 0)
                {
                    if (decimal.Parse(pdv[i][2].ToString()) > 0)
                    {
                        tc.Text = ((decimal.Parse(pdv[i][2].ToString())) * 100).ToString("0.0") + "%";
                    }
                    else
                    {
                        tc.Text = "0";
                    }
                }
                else
                {
                    tc.Text = "0";
                }
            }
            else
            {
                tc.Text = "没有任务";
            }
            tc.Attributes.Add("style", "background-image:none; border-collapse: collapse;white-space:nowrap;  border-width: 1px; border-style:solid;");

            tc.HorizontalAlign = HorizontalAlign.Center;
            tr.Cells.Add(tc);
            tb.Rows.Add(tr);
        }
    }

    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        ExportData("application/ms-excel", DateTime.Now.ToString("yyMdHm") + ".xls", true);
    }
    protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        init();
    }
    protected void ddlDepartment_SelectedIndexChanged(object sender, EventArgs e)
    {
        init();
    }
}
